Fetching and Visualizing Official Statistics with R

Author
Affiliation

Nicolas

Eesti Pank

Published

April 17, 2025

Interfaces to Official Statistics

  • Packages or set of classes and methods to read data and metadata documents through exchange frameworks
    • Use R (or Python) packages to read data from APIs, databases, and web pages
      • Individual packages:
        • eurostat: Access data from Eurostat
        • OECD: Access data from the OECD API
    • General-purpose packages:
      • rdbnomics: Meta-provider – Unified access to many economic databases (e.g. ECB, Eurostat, IMF, World Bank)

  • pxweb: Access to data sources using the PX-Web API (e.g. Statistics Sweden, Statistics Estonia)

DBnomics

  • DBnomics is a database of databases
    • free platform to aggregate publicly-available economic data provided by national and international statistical institutions, but also by researchers and private companies
    • Unified interface to access data from many sources
    • Harmonized data formats and metadata
    • Data series are available upon release by the provider
    • Each revision is archived to build a real-time database

How to fetch data (from DBnomics using R)

  • DBnomics R client
install.packages("rdbnomics")
library(rdbnomics)

Packages used in this tutorial

  • 📦 Fetching data (rdbnomics)
  • 🧹 Data wrangling and transformation (tidyverse)
  • 📊 Visualization (ggplot2, plotly)
  • 📋 Tabular summaries (gt)
  • 🧾 Building this presentation (quarto)
library(quarto)     # for compiling Quarto presentations
library(rdbnomics)  # for accessing economic data via DBnomics
library(tidyverse)  # dplyr, ggplot2, readr, etc.
library(plotly)     # interactive visualizations
library(gt)         # pretty tables

Example: Fetch Unemployment Data

  • Assume we know exactly the series ID we want to fetch
    • Unemployment rate, ILO definition, total, Estonia, from Eurostat
unemp <- rdb(ids = "Eurostat/ei_lmhr_m/M.PC_ACT.SA.LM-UN-T-TOT.EE") # fetch data

glimpse(unemp)
Rows: 296
Columns: 22
$ `@frequency`                      <chr> "monthly", "monthly", "monthly", "mo…
$ dataset_code                      <chr> "ei_lmhr_m", "ei_lmhr_m", "ei_lmhr_m…
$ dataset_name                      <chr> "Unemployment rate (%) - monthly dat…
$ freq                              <chr> "M", "M", "M", "M", "M", "M", "M", "…
$ geo                               <chr> "EE", "EE", "EE", "EE", "EE", "EE", …
$ `Geopolitical entity (reporting)` <chr> "Estonia", "Estonia", "Estonia", "Es…
$ indexed_at                        <dttm> 2024-10-31 15:26:51, 2024-10-31 15:…
$ indic                             <chr> "LM-UN-T-TOT", "LM-UN-T-TOT", "LM-UN…
$ Indicator                         <chr> "Unemployment according to ILO defin…
$ observations_attributes           <chr> "OBS_FLAG,", "OBS_FLAG,", "OBS_FLAG,…
$ original_period                   <chr> "2000-02", "2000-03", "2000-04", "20…
$ original_value                    <chr> "14.9", "14.2", "14.5", "13.9", "14"…
$ period                            <date> 2000-02-01, 2000-03-01, 2000-04-01,…
$ provider_code                     <chr> "Eurostat", "Eurostat", "Eurostat", …
$ s_adj                             <chr> "SA", "SA", "SA", "SA", "SA", "SA", …
$ `Seasonal adjustment`             <chr> "Seasonally adjusted data, not calen…
$ series_code                       <chr> "M.PC_ACT.SA.LM-UN-T-TOT.EE", "M.PC_…
$ series_name                       <chr> "Monthly – Percentage of population …
$ `Time frequency`                  <chr> "Monthly", "Monthly", "Monthly", "Mo…
$ unit                              <chr> "PC_ACT", "PC_ACT", "PC_ACT", "PC_AC…
$ `Unit of measure`                 <chr> "Percentage of population in the lab…
$ value                             <dbl> 14.9, 14.2, 14.5, 13.9, 14.0, 13.9, …
colnames(unemp)
 [1] "@frequency"                      "dataset_code"                   
 [3] "dataset_name"                    "freq"                           
 [5] "geo"                             "Geopolitical entity (reporting)"
 [7] "indexed_at"                      "indic"                          
 [9] "Indicator"                       "observations_attributes"        
[11] "original_period"                 "original_value"                 
[13] "period"                          "provider_code"                  
[15] "s_adj"                           "Seasonal adjustment"            
[17] "series_code"                     "series_name"                    
[19] "Time frequency"                  "unit"                           
[21] "Unit of measure"                 "value"                          

# Extract source and series ID from the metadata
(source_name <- unique(unemp$dataset_code))  
[1] "ei_lmhr_m"
(provider_code <- unique(unemp$provider_code))  
[1] "Eurostat"
(country_name <- unique(unemp$`Geopolitical entity (reporting)`)  )
[1] "Estonia"
(series_id <- unique(unemp$series_code))  
[1] "M.PC_ACT.SA.LM-UN-T-TOT.EE"

# Plot the data
p1 <- ggplot(unemp, aes(x = period, y = value)) +
  geom_line(color = "steelblue", linewidth = 1) +
  labs(
    title = paste("Unemployment Rate in ", country_name),
    subtitle = paste("Monthly, seasonally adjusted —", provider_code),
    x = "Date", y = "Percent",
    caption = paste("Source:", provider_code, "| Dataset:", source_name, "| ID:", series_id)
  ) +
  theme_minimal()
p1


Interactive plot

ggplotly(p1)

How do we find the series ID/mask/dimensions?

  • Go to the DBnomics website
    • Search directly for a series or pick a provider
    • Search for the data you want (dataset_code)
    • Click on the series (series_code)
    • Copy the series ID from the URL

  • Show the available datasets of a provider:
head(rdb_datasets(provider_code = "Eurostat"))
$Eurostat
              code
            <char>
   1:   aact_ali01
   2:   aact_ali02
   3:   aact_eaa01
   4:   aact_eaa02
   5:   aact_eaa03
  ---             
8289: yth_empl_120
8290: yth_empl_130
8291: yth_empl_130
8292: yth_empl_140
8293: yth_empl_140
                                                                                        name
                                                                                      <char>
   1:       Agricultural labour input statistics: absolute figures (1 000 annual work units)
   2:                                          Agricultural labour input statistics: indices
   3:                           Economic accounts for agriculture - values at current prices
   4:                               Economic accounts for agriculture - values at n-1 prices
   5:             Economic accounts for agriculture - values at constant prices (2015 = 100)
  ---                                                                                       
8289:                 Youth long-term unemployment rate (12 months or longer) by sex and age
8290: Youth long-term unemployment rate (12 months or longer) by sex, age and NUTS 2 regions
8291:  Youth long-term unemployment rate (12 months or longer) by sex, age and NUTS 2 region
8292:                                Youth unemployment ratio by sex, age and NUTS 2 regions
8293:                                 Youth unemployment ratio by sex, age and NUTS 2 region

  • Show the dimensions of a dataset:
head(rdb_dimensions(provider_code = "Eurostat", dataset_code = "ei_lmhr_m"))
$Eurostat
$Eurostat$ei_lmhr_m
$Eurostat$ei_lmhr_m$freq
     freq Time frequency
   <char>         <char>
1:      M        Monthly

$Eurostat$ei_lmhr_m$geo
          geo           Geopolitical entity (reporting)
       <char>                                    <char>
 1:        AT                                   Austria
 2:        BA                    Bosnia and Herzegovina
 3:        BE                                   Belgium
 4:        BG                                  Bulgaria
 5:        CH                               Switzerland
 6:        CY                                    Cyprus
 7:        CZ                                   Czechia
 8:        DE                                   Germany
 9:        DK                                   Denmark
10:      EA20      Euro area – 20 countries (from 2023)
11:        EE                                   Estonia
12:        EL                                    Greece
13:        ES                                     Spain
14: EU27_2020 European Union - 27 countries (from 2020)
15:        FI                                   Finland
16:        FR                                    France
17:        HR                                   Croatia
18:        HU                                   Hungary
19:        IE                                   Ireland
20:        IS                                   Iceland
21:        IT                                     Italy
22:        JP                                     Japan
23:        LT                                 Lithuania
24:        LU                                Luxembourg
25:        LV                                    Latvia
26:        MT                                     Malta
27:        NL                               Netherlands
28:        NO                                    Norway
29:        PL                                    Poland
30:        PT                                  Portugal
31:        RO                                   Romania
32:        SE                                    Sweden
33:        SI                                  Slovenia
34:        SK                                  Slovakia
35:        TR                                   Türkiye
36:        UK                            United Kingdom
37:        US                             United States
          geo           Geopolitical entity (reporting)

$Eurostat$ei_lmhr_m$indic
          indic
         <char>
1: LM-UN-F-GT25
2: LM-UN-F-LE25
3:  LM-UN-F-TOT
4: LM-UN-M-GT25
5: LM-UN-M-LE25
6:  LM-UN-M-TOT
7: LM-UN-T-GT25
8: LM-UN-T-LE25
9:  LM-UN-T-TOT
                                                             Indicator
                                                                <char>
1:  Unemployment according to ILO definition - over 25 years - females
2: Unemployment according to ILO definition - under 25 years - females
3:                  Unemployment according to ILO definition - females
4:    Unemployment according to ILO definition - over 25 years - males
5:   Unemployment according to ILO definition - under 25 years - males
6:                    Unemployment according to ILO definition - males
7:    Unemployment according to ILO definition - over 25 years - total
8:   Unemployment according to ILO definition - under 25 years - total
9:                    Unemployment according to ILO definition - total

$Eurostat$ei_lmhr_m$s_adj
    s_adj
   <char>
1:    NSA
2:     SA
                                                             Seasonal adjustment
                                                                          <char>
1: Unadjusted data (i.e. neither seasonally adjusted nor calendar adjusted data)
2:                          Seasonally adjusted data, not calendar adjusted data

$Eurostat$ei_lmhr_m$unit
     unit                              Unit of measure
   <char>                                       <char>
1: PC_ACT Percentage of population in the labour force

  • Query to filter/select series from a provider’s dataset
head(rdb_series(
  provider = "Eurostat",
  dataset_code = "ei_lmhr_m",
  query = "United Kingdom"
))
$Eurostat
$Eurostat$ei_lmhr_m
                     series_code
                          <char>
 1: M.PC_ACT.NSA.LM-UN-F-GT25.UK
 2: M.PC_ACT.NSA.LM-UN-F-LE25.UK
 3:  M.PC_ACT.NSA.LM-UN-F-TOT.UK
 4: M.PC_ACT.NSA.LM-UN-M-GT25.UK
 5: M.PC_ACT.NSA.LM-UN-M-LE25.UK
 6:  M.PC_ACT.NSA.LM-UN-M-TOT.UK
 7: M.PC_ACT.NSA.LM-UN-T-GT25.UK
 8: M.PC_ACT.NSA.LM-UN-T-LE25.UK
 9:  M.PC_ACT.NSA.LM-UN-T-TOT.UK
10:  M.PC_ACT.SA.LM-UN-F-GT25.UK
11:  M.PC_ACT.SA.LM-UN-F-LE25.UK
12:   M.PC_ACT.SA.LM-UN-F-TOT.UK
13:  M.PC_ACT.SA.LM-UN-M-GT25.UK
14:  M.PC_ACT.SA.LM-UN-M-LE25.UK
15:   M.PC_ACT.SA.LM-UN-M-TOT.UK
16:  M.PC_ACT.SA.LM-UN-T-GT25.UK
17:  M.PC_ACT.SA.LM-UN-T-LE25.UK
18:   M.PC_ACT.SA.LM-UN-T-TOT.UK
                                                                                                                                                                                                                      series_name
                                                                                                                                                                                                                           <char>
 1:  Monthly – Percentage of population in the labour force – Unadjusted data (i.e. neither seasonally adjusted nor calendar adjusted data) – Unemployment according to ILO definition - over 25 years - females – United Kingdom
 2: Monthly – Percentage of population in the labour force – Unadjusted data (i.e. neither seasonally adjusted nor calendar adjusted data) – Unemployment according to ILO definition - under 25 years - females – United Kingdom
 3:                  Monthly – Percentage of population in the labour force – Unadjusted data (i.e. neither seasonally adjusted nor calendar adjusted data) – Unemployment according to ILO definition - females – United Kingdom
 4:    Monthly – Percentage of population in the labour force – Unadjusted data (i.e. neither seasonally adjusted nor calendar adjusted data) – Unemployment according to ILO definition - over 25 years - males – United Kingdom
 5:   Monthly – Percentage of population in the labour force – Unadjusted data (i.e. neither seasonally adjusted nor calendar adjusted data) – Unemployment according to ILO definition - under 25 years - males – United Kingdom
 6:                    Monthly – Percentage of population in the labour force – Unadjusted data (i.e. neither seasonally adjusted nor calendar adjusted data) – Unemployment according to ILO definition - males – United Kingdom
 7:    Monthly – Percentage of population in the labour force – Unadjusted data (i.e. neither seasonally adjusted nor calendar adjusted data) – Unemployment according to ILO definition - over 25 years - total – United Kingdom
 8:   Monthly – Percentage of population in the labour force – Unadjusted data (i.e. neither seasonally adjusted nor calendar adjusted data) – Unemployment according to ILO definition - under 25 years - total – United Kingdom
 9:                    Monthly – Percentage of population in the labour force – Unadjusted data (i.e. neither seasonally adjusted nor calendar adjusted data) – Unemployment according to ILO definition - total – United Kingdom
10:                           Monthly – Percentage of population in the labour force – Seasonally adjusted data, not calendar adjusted data – Unemployment according to ILO definition - over 25 years - females – United Kingdom
11:                          Monthly – Percentage of population in the labour force – Seasonally adjusted data, not calendar adjusted data – Unemployment according to ILO definition - under 25 years - females – United Kingdom
12:                                           Monthly – Percentage of population in the labour force – Seasonally adjusted data, not calendar adjusted data – Unemployment according to ILO definition - females – United Kingdom
13:                             Monthly – Percentage of population in the labour force – Seasonally adjusted data, not calendar adjusted data – Unemployment according to ILO definition - over 25 years - males – United Kingdom
14:                            Monthly – Percentage of population in the labour force – Seasonally adjusted data, not calendar adjusted data – Unemployment according to ILO definition - under 25 years - males – United Kingdom
15:                                             Monthly – Percentage of population in the labour force – Seasonally adjusted data, not calendar adjusted data – Unemployment according to ILO definition - males – United Kingdom
16:                             Monthly – Percentage of population in the labour force – Seasonally adjusted data, not calendar adjusted data – Unemployment according to ILO definition - over 25 years - total – United Kingdom
17:                            Monthly – Percentage of population in the labour force – Seasonally adjusted data, not calendar adjusted data – Unemployment according to ILO definition - under 25 years - total – United Kingdom
18:                                             Monthly – Percentage of population in the labour force – Seasonally adjusted data, not calendar adjusted data – Unemployment according to ILO definition - total – United Kingdom

Fetch two (or more) series at once

  • Example: Balance of Payments (BOP) for France and Germany from the IMF for Current Account, Total, Net, Euros, Millions, Annual
# by ID
bop <- rdb(ids = c("IMF/BOP/A.FR.BCA_BP6_EUR", "IMF/BOP/A.DE.BCA_BP6_EUR"))
bop %>% count(`Reference Area`)
   Reference Area     n
           <char> <int>
1:         France    15
2:        Germany    26
# by Mask
bop <- rdb(provider = "IMF",
           dataset_code =  "BOP",
           mask = "A.FR+DE.BCA_BP6_EUR")
bop %>% count(`Reference Area`)
   Reference Area     n
           <char> <int>
1:         France    15
2:        Germany    26

⚠You can not specify a dimension without a value!

# by Dimension
dim <- list(
  REF_AREA = c("DE", "FR"),
  INDICATOR = c("BCA_BP6_EUR"), 
  FREQ = "A"
)
## Here I do not include FREQUENCY in the dimension list. I would download annual and quarterly data
# dim <- list(
#   REF_AREA = c("DE", "FR"),
#   INDICATOR = c("BCA_BP6_EUR")
# ) 
bop <- rdb(provider = "IMF", dataset_code = "BOP", dimensions = dim)
bop %>% count(`Reference Area`)
   Reference Area     n
           <char> <int>
1:         France    15
2:        Germany    26

# Line plot with color by country
p2 <- ggplot(bop, aes(x = period, y = value, color = `Reference Area`)) +
  geom_step(linewidth = 1) +
  labs(
    title = "Balance of Payments (BCA, EUR)",
    subtitle = "France vs Germany — Annual",
    x = "Year",
    y = "EUR (Millions)",
    caption = "Source: IMF / DBnomics"
  ) +
  theme_minimal()
p2

Fetch two series from different datasets of different providers

unemp2 <- rdb(ids = c("AMECO/ZUTN/EA19.1.0.0.0.ZUTN", "Eurostat/une_rt_q/Q.SA.Y15-24.PC_ACT.T.EA19"))
# See which providers and datasets are included
dim(unemp2)
[1] 122  27
unique(unemp2$provider_code)
[1] "AMECO"    "Eurostat"
unique(unemp2$dataset_code)
[1] "ZUTN"     "une_rt_q"
unique(unemp2$series_code)
[1] "EA19.1.0.0.0.ZUTN"         "Q.SA.Y15-24.PC_ACT.T.EA19"
unique(unemp2$`@frequency`)
[1] "annual"    "quarterly"
unique(unemp2$`Seasonal adjustment`)
[1] NA                                                    
[2] "Seasonally adjusted data, not calendar adjusted data"

# Summarize coverage and data availability
unemp2_summary <- unemp2 %>%
  group_by(series_code) %>%
  summarize(
    provider = first(provider_code),
    dataset = first(dataset_code),
    start_all = min(period, na.rm = TRUE),
    end_all = max(period, na.rm = TRUE),
    start_data = min(period[!is.na(value)]),
    end_data = max(period[!is.na(value)]),
    n_obs = sum(!is.na(value)),
    .groups = "drop"
  )

unemp2_summary_table <- unemp2_summary |> 
gt() %>%
  tab_header(
    title = "Time Coverage and Non-Missing Observations",
    subtitle = "For Each Series from AMECO and Eurostat"
  ) %>%
  cols_label(
    series_code = "Series ID",
    provider = "Provider",
    dataset = "Dataset",
    start_all = "Start (all)",
    end_all = "End (all)",
    start_data = "Start (non-NA)",
    end_data = "End (non-NA)",
    n_obs = "# Obs"
  ) %>%
  fmt_date(
    columns = c(start_all, end_all, start_data, end_data),
    date_style = "iso"
  ) %>%
  tab_options(
    table.width = pct(100),
    column_labels.font.weight = "bold"
  )

unemp2_summary_table
Time Coverage and Non-Missing Observations
For Each Series from AMECO and Eurostat
Series ID Provider Dataset Start (all) End (all) Start (non-NA) End (non-NA) # Obs
EA19.1.0.0.0.ZUTN AMECO ZUTN 1960-01-01 2026-01-01 1997-01-01 2026-01-01 30
Q.SA.Y15-24.PC_ACT.T.EA19 Eurostat une_rt_q 2009-01-01 2022-07-01 2009-01-01 2022-07-01 55

# Metadata vectors
providers <- unique(unemp2$provider_code)
datasets <- unique(unemp2$dataset_code)
series_ids <- unique(unemp2$series_code)
# Create a label that combines dataset + series ID
unemp2_clean <- unemp2 %>%
  drop_na(value) %>%
  mutate(label = case_when(
    series_code == "EA19.1.0.0.0.ZUTN" ~ "Total, AMECO",
    series_code == "Q.SA.Y15-24.PC_ACT.T.EA19" ~ "Youth (15–24), Eurostat",
    TRUE ~ series_code
  ))

p3 <- ggplot(unemp2_clean, aes(x = period, y = value, color = label)) +
  geom_line(linewidth = 1) +
  labs(
    title = "Unemployment Rates from Multiple Sources (EA19)",
    subtitle = "AMECO and Eurostat — Different definitions",
    x = "Year", y = "Percent",
    caption = paste("Series IDs:", paste(unique(unemp2_clean$series_code), collapse = " | "))
  ) +
  theme_minimal()
p3

Fetch large amounts of data

  • Sometimes you need to fetch many if not all dimensions of the data
  • You can wildcard dimension and post-filter
  • Example: MFI Interest Rate Statistics from the ECB
mir_mortgage_ee <- rdb("ECB", "MIR", "M.EE.B.A2C.A.R.A.2250.EUR.N")
unique(mir_mortgage_ee$series_name)
[1] "Monthly – Estonia – Deposit-taking corporations except the central bank (S.122) – Lending for house purchase excluding revolving loans and overdrafts, convenience and extended credit card debt – Total – Annualised agreed rate (AAR) / Narrowly defined effective rate (NDER) – Total – Households and non-profit institutions serving households (S.14 and S.15) – Euro – New business"

Wildcarding dimensions

  • To fetch multiple values for a dimension (e.g. countries), just remove the value from that position
    • Example: remove "EE" to fetch all countries (REF_AREA)

⚠️ This can take a while

# mir_mortgage_ee <- rdb("ECB", "MIR", "M.EE.B.A2C.A.R.A.2250.EUR.N")
mir <- rdb("ECB", "MIR", "M..B..A.R.A..EUR.N")
dim(mir)
[1] 105479     31
format(object.size(mir), units = "Mb")
[1] "25.3 Mb"
unique(mir$REF_AREA)
 [1] "AT" "BE" "CY" "DE" "EE" "ES" "FI" "FR" "GR" "HR" "IE" "IT" "LT" "LU" "LV"
[16] "MT" "NL" "PT" "SI" "SK" "U2"
unique(mir$BS_ITEM)
 [1] "A2A"  "A2AC" "A2B"  "A2BC" "A2C"  "A2CC" "A2D"  "A2Z"  "A2Z1" "A2Z3"
[11] "L21"  "L22"  "L23"  "L24" 
unique(mir$`BS counterpart sector`)
[1] "Non-Financial corporations (S.11)"                                           
[2] "Households and non-profit institutions serving households (S.14 and S.15)"   
[3] "Households of which sole proprietors and unincorporated partnerships (SP/UP)"
[4] "Non-Financial corporations and Households (S.11 and S.14 and S.15)"          

Filter and plot

  • Filter Estonia, Latvia and Lithuania
  • Keep only selected BS items (loan categories)
# Filter by BS_ITEM and countries
mir_filtered <- mir %>%
  filter(
    REF_AREA %in% c("EE", "LV", "LT"),
    BS_ITEM %in% c("A2I", "A2C", "A2B", "A2J", "A2A")
  )

Plot interest rates by country & type

country_list <- paste(sort(unique(mir_filtered$REF_AREA)), collapse = ", ")
item_list <- paste(unique(mir_filtered$BS_ITEM), collapse = ", ")

caption_text <- paste(
  "Source: ECB / DBnomics — Dataset code: MIR",
  paste0("\nFiltered: REF_AREA in ", country_list, "; BS_ITEM in ", item_list)
)

mir_filtered <- mir_filtered %>%
  mutate(facet_label = paste0(`BS counterpart sector`, ".\n\n", `Balance sheet item`))

p4 <- ggplot(mir_filtered, aes(x = period, y = value, color = REF_AREA)) +
  geom_line(linewidth = 0.8) +
  facet_wrap(~ facet_label, labeller = label_wrap_gen(width = 30), ncol = 3) +
  labs(
    title = "Interest Rates for Households and Firms",
    subtitle = "Faceted by Loan Type and Borrower Sector",
    x = "Date", y = "Percent",
    caption = caption_text
  ) +
  theme_minimal() +
  theme(legend.position = "bottom")
p4

How to Fetch Data from Statistics Estonia in R

  • Statistics Estonia is not yet available through DBnomics.
  • There is no dedicated R package for accessing data from andmed.stat.ee.
  • However, Statistics Estonia provides a PX-WEB API, which can be accessed from R using the pxweb package.
  • pxweb is a general-purpose client for PX-WEB APIs used by many statistical agencies (e.g., Sweden, Finland, Estonia).
install.packages("pxweb")
library(pxweb)

Accessing Statistics Estonia Data with pxweb

  • Two main approaches:
    • pxweb_interactive() – guided interface in console
    • pxweb_get() – programmatic access

Interactive Access with pxweb_interactive()

  • Launch a guided interface for browsing and selecting data

  • Choose from top-level domains (e.g. Economy, Population)

  • Filter by category, time period, etc.

  • Preview results and download the dataset

  • Automatically generates reproducible R code

  • Top-level database address is:


pxweb_interactive("https://andmed.stat.ee/api/v1/en/stat")

    ============================================================================================================================
     R PXWEB: Content of 'andmed.stat.ee'
              at '/api/v1/en/stat'
    ============================================================================================================================
     [ 1 ] : Environment
     [ 2 ] : Economy
     [ 3 ] : Population
     [ 4 ] : Social life
     [ 5 ] : Multidomain statistics
     [ 6 ] : Population and Housing Census
     [ 7 ] : Discontinued datasets
    ============================================================================================================================
    Enter your choice:
    ('esc' = Quit, 'b' = Back, 'i' = Show id)
    1:

    Do you want to print code to query and download data?
    Enter your choice:
    ('esc' = Quit, 'y' = Yes, 'n' = No)
    1: y
    Do you want to print query in json format (otherwise query is printed as an R list)?
    Enter your choice:
    ('esc' = Quit, 'y' = Yes, 'n' = No)
    1: n
    Do you want to download the data?
    Enter your choice:
    ('esc' = Quit, 'y' = Yes, 'n' = No)
    1: y
    Do you want to return a the data as a data.frame?
    Enter your choice:
    ('esc' = Quit, 'y' = Yes, 'n' = No)
    1: y
    Do you want to print citation for the data?
    Enter your choice:
    ('esc' = Quit, 'y' = Yes, 'n' = No)
    1: n


Direct Query with pxweb_get()

library(pxweb)
pa111_meta <- pxweb_get(url = "https://andmed.stat.ee/api/v1/en/stat/PA111")
pa111_meta
PXWEB METADATA
PA111: AVERAGE MONTHLY GROSS WAGES (SALARIES), MEDIAN, DECILES AND NUMBER OF EMPLOYEES BY ECONOMIC ACTIVITY SECTION (QUARTERLY) 
variables:
 [[1]] Näitaja: Indicator
 [[2]] Tegevusala: Economic activity
 [[3]] Vaatlusperiood: Reference period
pa111_meta$variables[[1]]
$code
[1] "Näitaja"

$text
[1] "Indicator"

$values
 [1] "GR_W_AVG"    "NR_EMPL"     "GR_W_D1"     "GR_W_D2"     "GR_W_D3"    
 [6] "GR_W_D4"     "GR_W_D5"     "GR_W_D6"     "GR_W_D7"     "GR_W_D8"    
[11] "GR_W_D9"     "GR_W_AVG_SM"

$valueTexts
 [1] "Average monthly gross wages (salaries), euros"                                                  
 [2] "Number of employees"                                                                            
 [3] "1st decile of monthly gross wages (salaries), euros"                                            
 [4] "2nd decile of monthly gross wages (salaries), euros"                                            
 [5] "3rd decile of monthly gross wages (salaries), euros"                                            
 [6] "4th decile of monthly gross wages (salaries), euros"                                            
 [7] "Median (5th decile) of monthly gross wages (salaries), euros"                                   
 [8] "6th decile of monthly gross wages (salaries), euros"                                            
 [9] "7th decile of monthly gross wages (salaries), euros"                                            
[10] "8th decile of monthly gross wages (salaries), euros"                                            
[11] "9th decile of monthly gross wages (salaries), euros"                                            
[12] "Percentage change in average gross wages (salaries) compared to same period in previous year, %"

$elimination
[1] FALSE

$time
[1] FALSE

How to Identify Query Variables

  • You need to know the variable names to build a pxweb_get() query
  • Two main ways to discover them:
  1. Programmatically
    • Use the metadata from the pxweb object (e.g. pa111_list)
    • Example fields:
      • Näitaja: Indicator
      • Tegevusala: Economic activity
      • Vaatlusperiood: Reference period
  2. Via the Web Portal
    • Go to andmed.stat.ee
    • Find your table and click:
      “API query for this table”
    • Shows all available dimensions and codes

px_query_list1 <- list(
  "Näitaja" = c("GR_W_D1","GR_W_D5", "GR_W_D7", "GR_W_D9"),
  "Tegevusala" = "*",
  "Vaatlusperiood" = "*"
)

pa111 <-
  pxweb_get(url = "https://andmed.stat.ee/api/v1/en/stat/PA111",
            query = px_query_list1)
glimpse(pa111)
List of 7
 $ columns       :List of 4
  ..$ :List of 3
  .. ..$ code: chr "Näitaja"
  .. ..$ text: chr "Indicator"
  .. ..$ type: chr "d"
  ..$ :List of 3
  .. ..$ code: chr "Tegevusala"
  .. ..$ text: chr "Economic activity"
  .. ..$ type: chr "d"
  ..$ :List of 3
  .. ..$ code: chr "Vaatlusperiood"
  .. ..$ text: chr "Reference period"
  .. ..$ type: chr "t"
  ..$ :List of 3
  .. ..$ code: chr "PA111: AVERAGE MONTHLY GROSS WAGES (SALARIES), MEDIAN, DECILES AND NUMBER OF EMPLOYEES"
  .. ..$ text: chr "PA111: AVERAGE MONTHLY GROSS WAGES (SALARIES), MEDIAN, DECILES AND NUMBER OF EMPLOYEES"
  .. ..$ type: chr "c"
 $ comments      : list()
 $ data          :List of 1280
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  ..$ :List of 2
  .. ..$ key   :List of 3
  .. ..$ values:List of 1
  .. [list output truncated]
 $ metadata      :List of 1
  ..$ :List of 3
  .. ..$ updated: chr "9999-12-31T21:59:59Z"
  .. ..$ label  : chr "PA111: AVERAGE MONTHLY GROSS WAGES (SALARIES), MEDIAN, DECILES AND NUMBER OF EMPLOYEES by Indicator, Economic a"| __truncated__
  .. ..$ source : chr "Statistics Estonia"
 $ pxweb_metadata:List of 2
  ..$ title    : chr "PA111: AVERAGE MONTHLY GROSS WAGES (SALARIES), MEDIAN, DECILES AND NUMBER OF EMPLOYEES BY ECONOMIC ACTIVITY SEC"| __truncated__
  ..$ variables:List of 3
  .. ..$ :List of 6
  .. ..$ :List of 6
  .. ..$ :List of 6
  ..- attr(*, "class")= chr [1:2] "pxweb_metadata" "list"
 $ url           : chr "https://andmed.stat.ee/api/v1/en/stat/PA111"
 $ time_stamp    : POSIXct[1:1], format: "2025-03-28 13:41:04"
 - attr(*, "class")= chr [1:2] "pxweb_data" "list"
pcite <- pxweb_cite(pa111)
Statistics Estonia (2025). "PA111: AVERAGE MONTHLY GROSS WAGES
(SALARIES), MEDIAN, DECILES AND NUMBER OF EMPLOYEES BY ECONOMIC
ACTIVITY SECTION (QUARTERLY)." [Data accessed 2025-03-28
13:41:04.819378 using pxweb R package 0.17.0],
<https://andmed.stat.ee/api/v1/en/stat/PA111>.

A BibTeX entry for LaTeX users is

  @Misc{,
    title = {PA111: AVERAGE MONTHLY GROSS WAGES (SALARIES), MEDIAN, DECILES AND NUMBER OF EMPLOYEES BY ECONOMIC ACTIVITY SECTION (QUARTERLY)},
    author = {{Statistics Estonia}},
    organization = {Statistics Estonia},
    address = {Tallinn, Estonia},
    year = {2025},
    url = {https://andmed.stat.ee/api/v1/en/stat/PA111},
    note = {[Data accessed 2025-03-28 13:41:04.819378 using pxweb R package 0.17.0]},
  }
Kindly cite the pxweb R package as follows:

  Mans Magnusson, Markus Kainu, Janne Huovari, and Leo Lahti
  (rOpenGov).  pxweb: R tools for PXWEB API.  URL:
  http://github.com/ropengov/pxweb

A BibTeX entry for LaTeX users is

  @Misc{,
    title = {pxweb: R tools for PX-WEB API},
    author = {Mans Magnusson and Markus Kainu and Janne Huovari and Leo Lahti},
    year = {2019},
  }

pa111.df <- as.data.frame(pa111, column.name.type = "text", variable.value.type = "text")
pa111_code.df <- as.data.frame(pa111, column.name.type = "code", variable.value.type = "code")
lapply(pa111.df, unique,2)
$Indicator
[1] "1st decile of monthly gross wages (salaries), euros"         
[2] "Median (5th decile) of monthly gross wages (salaries), euros"
[3] "7th decile of monthly gross wages (salaries), euros"         
[4] "9th decile of monthly gross wages (salaries), euros"         

$`Economic activity`
 [1] "Total - all activities"                                              
 [2] "Agriculture, forestry and fishing"                                   
 [3] "Mining and quarrying"                                                
 [4] "Manufacturing"                                                       
 [5] "Electricity, gas, steam and air conditioning supply"                 
 [6] "Water supply; sewerage, waste management and remediation activities" 
 [7] "Construction"                                                        
 [8] "Wholesale and retail trade; repair of motor vehicles and motorcycles"
 [9] "Transportation and storage"                                          
[10] "Accommodation and food service activities"                           
[11] "Information and communication"                                       
[12] "Financial and insurance activities"                                  
[13] "Real estate activities"                                              
[14] "Professional, scientific and technical activities"                   
[15] "Administrative and support service activities"                       
[16] "Public administration and defence; compulsory social security"       
[17] "Education"                                                           
[18] "Human health and social work activities"                             
[19] "Arts, entertainment and recreation"                                  
[20] "Other service activities"                                            

$`Reference period`
 [1] "2021 Q1" "2021 Q2" "2021 Q3" "2021 Q4" "2022 Q1" "2022 Q2" "2022 Q3"
 [8] "2022 Q4" "2023 Q1" "2023 Q2" "2023 Q3" "2023 Q4" "2024 Q1" "2024 Q2"
[15] "2024 Q3" "2024 Q4"

$`PA111: AVERAGE MONTHLY GROSS WAGES (SALARIES), MEDIAN, DECILES AND NUMBER OF EMPLOYEES`
   [1]  532  472  508  560  563  609  600  651  659  700  668  724  730  771
  [15]  735  796  500  452  411  522  520  543  536  597  599  595  582  654
  [29]  640  680  727  666  664  778  701  786  942  804  781  850 1036  888
  [43]  864 1000 1090  557  564  584  586  660  688  693  737  761  776  820
  [57]  840  855  868  831  891  964  960 1008 1088 1207 1139 1191 1128 1305
  [71] 1206 1303  589  567  674  713  774  760  788  829  830  459  444  495
  [85]  497  487  548  577  554  580  612  623  573  605  633  650  526  438
  [99]  514  510  546  581  614  615  657  675  690  683  752  762  755  553
 [113]  540  636  718  725  736  780  797  799  359  200  326  383  385  462
 [127]  499  455  482  498  535  569  883  851  881  907  947 1035 1094 1100
 [141] 1143 1126 1169 1200 1216 1204 1247 1044 1066 1147 1326 1411 1345 1370
 [155] 1444 1513 1542 1570 1621  207  205  216  223  237  249  250  267  271
 [169]  288  300  307  318  574  765  800  812  400  341  380  434  465  448
 [183]  490  489  519  486  512  550  576  558  579  723  739  927  871  972
 [197]  904 1107 1181 1210 1089 1284 1231 1293 1290  606  631  679  733  782
 [211]  828  880  622  909  616  632  665  649  728  738  759  753  825  889
 [225]  991  502  317  320  398  687  689  414  731  324  277  290  363  372
 [239]  375  415  426  453  436  451  471  456  475 1158 1199 1270 1240 1354
 [253] 1349 1429 1424 1524 1500 1578 1553 1641 1620 1699 1001 1037 1097 1078
 [267] 1159 1192 1258 1297 1341 1300 1357 1400 1462 1459 1560 1639 1690 1724
 [281] 1836 1990 1862 1920 2045 1896 2050 2110 2268 1118 1220 1265 1194 1358
 [295] 1363 1422 1342 1483 1487 1534 1465 1587 1589 1651 1588 1766 1693 1695
 [309] 1821 1958 1900 1915 2066 2328 2100 2164 2125 2614 2253 2334 1311 1324
 [323] 1365 1317 1368 1436 1527 1488 1547 1616 1683 1662 1661 1722 1816 1033
 [337] 1081 1144 1167 1257 1286 1215 1312 1360 1292 1372 1399 1430  998 1054
 [351] 1085 1168 1202 1222 1307 1310 1379 1391 1080 1109 1318 1415 1412 1439
 [365] 1506 1525 1540  704  511  803  837  965  966  984 1074 1056 1077 1123
 [379] 1149 1134 2200 2295 2350 2467 2500 2617 2661 2768 2728 2812 2873 2955
 [393] 2996 3100 2000 2095 1926 1924 2127 2216 2090 2227 2395 2450 2353 2400
 [407] 2555 2682 2594 2650  667  703  720  766  790  805  813  867  893  913
 [421]  917  967  980 1395 1431 1471 1522 1520 1610 1654 1756 1749 1833 1840
 [435] 1913 1905 1978 2069  921  896  970  990 1014 1102 1120 1129 1197 1177
 [449] 1236 1313 1301 1612 1675 1812 1789 1875 2067 2135 2303 2300 2283 2443
 [463] 1350 1205 1406 1463 1546 1637 1780 1602 1850 1782 1909 1726 1960 1445
 [477] 1574 1564 1617 1562 1783 1820 1886 1841 1991 2017 2079  996  995 1140
 [491] 1083 1250 1280 1333 1239 1408 1374 1476  779  842  892  934  962 1027
 [505] 1029 1043 1556 1623 1688 1845 1827 1944 1933 2080 2028 2151 2238 2330
 [519] 1427 1477 1448 1549 1627 1720 1763 1746 1861 1765 1846 1987 1934 2091
 [533] 2177 2207 1965 2254 2360 2535 2413 2412 2580 2414 2585 2854 1466 1595
 [547] 1596 1656 1778 1856 1760 1922 1992 1908 2064 2059 2150 2133 2481 2196
 [561] 2206 2764 2509 2731 3181 2675 2799 2800 3556 2863 2949 1652 1657 1714
 [575] 1685 1745 1810 1919 1891 1973 2029 2115 2106 2086 2179 2247 1481 1634
 [589] 1702 1663 1794 1823 1898 1997 1380 1460 1614 1650 1773 1822 1806 1894
 [603] 1901 1948 1409 1470 1531 1686 1741 1740 1864 1895 2013 2009 2049  711
 [617] 1061 1188 1256 1276 1335 1393 1373 1435 1453 2988 3116 3093 3227 3309
 [631] 3423 3454 3695 3712 3864 3806 3900 4000 4100 4126 4256 2692 2809 2604
 [645] 2900 2995 2817 3194 3250 3117 3207 3450 3525 3401 3506 1016 1022 1071
 [659] 1113 1172 1242 1273 1320 1403 1450 1510 1983 2092 2205 2274 2407 2605
 [673] 2593 2687 2700 2867 1291 1447 1509 1550 1606 1710 1700 1694 1743 1807
 [687] 1839 2323 2333 2613 2663 2608 2850 2834 3006 1541 1718 1491 1727 1622
 [701] 1857 1892 1957 2230 1963 2272 2118 2408 2142 2430 1798 2020 1951 2062
 [715] 1972 2163 2224 2474 2513 2590 2515 2750 2792 2876 1150 1461 1472 1583
 [729] 1677 1613 1050 1114 1272 1308 1316 1433 1441 1508 1521 2521 2707 2600
 [743] 2767 2776 3000 2940 3126 3136 3395 3238 3431 3417 3625 3500 3745 2174
 [757] 2237 2342 2456 2559 2689 2609 2899 2639 2832 2980 2760 3067 2655 2944
 [771] 2979 2984 2713 3046 3198 3370 3005 3347 3513 3514 3524 3755 2217 2369
 [785] 2457 2627 2632 2734 2666 2926 2839 3092 3049 3201 3200 4020 3196 3633
 [799] 5035 3667 4067 5548 3904 4182 4248 5605 4122 4200 2371 2349 2347 2441
 [813] 2451 2601 2714 2716 2811 2856 2969 3044 3023 3231 2202 2401 2404 2532
 [827] 2366 2636 2781 2637 3034 2852 3038 3265 2239 2375 2464 2640 2662 2770
 [841] 2751 2860 2848 2950 2908 3030 3015 3176 2171 2290 2273 2325 2544 2568
 [855] 2932 2808 2922 3074 3025 3148 1407 1538 1625 1777 1893 1911 1989 2055
 [869] 2030 2221 2139 4557 4833 4790 4865 5098 5242 5286 5506 5610 5723 5697
 [883] 5729 5990 6100 6169 6252 4266 4450 4061 4180 4709 4767 4475 4672 5060
 [897] 5043 4800 5000 5382 5346 5100 5403 1945 2003 2005 2078 2159 2310 2429
 [911] 2484 2721 2685 2864 2814 2998 3058 3159 3314 3464 3549 3604 3843 3861
 [925] 3943 3933 4092 4197 4284 4317 4598 2204 2293 2345 2483 2583 2679 3027
 [939] 3031 3167 3209 3291 2951 3419 3225 3338 3263 3773 3730 3578 4072 4015
 [953] 3824 3833 4300 2071 2576 2039 2365 2212 2264 2621 2629 3277 3147 2902
 [967] 3550 3435 3460 3266 3468 3377 3717 3672 3813 3685 4232 4254 4413 4318
 [981] 4732 4728 4888 1824 1874 2075 2015 2312 2502 2753 1800 1883 1968 2072
 [995] 2111 2287 2336 2488 2417 2512 2506 2683

# Convert quarter to a Date object for plotting
pa111_clean <- pa111.df %>%
  mutate(
    quarter = yq(str_replace(`Reference period`, " ", "-"))
  ) %>%
  filter(Indicator %in% c(
    "1st decile of monthly gross wages (salaries), euros",
    "Median (5th decile) of monthly gross wages (salaries), euros",
    "9th decile of monthly gross wages (salaries), euros"
  ))

# Optional: select a few key sectors
selected_sectors <- c("Manufacturing", "Information and communication", "Financial and insurance activities")

pa111_plotdata <- pa111_clean %>%
  filter(`Economic activity` %in% selected_sectors) %>%
  mutate(Indicator = recode(Indicator,
    "1st decile of monthly gross wages (salaries), euros" = "D1",
    "Median (5th decile) of monthly gross wages (salaries), euros" = "Median",
    "9th decile of monthly gross wages (salaries), euros" = "D9"
  )) %>%
  pivot_wider(
    names_from = Indicator,
    values_from = `PA111: AVERAGE MONTHLY GROSS WAGES (SALARIES), MEDIAN, DECILES AND NUMBER OF EMPLOYEES`
  )

# Create the ribbon plot
ggplot(pa111_plotdata, aes(x = quarter)) +
  geom_ribbon(aes(ymin = D1, ymax = D9), fill = "skyblue", alpha = 0.5) +
  geom_line(aes(y = Median), color = "darkblue", linewidth = 1) + 
  facet_wrap(~`Economic activity`) +
  labs(
    title = "Wage Distribution by Sector",
    subtitle = "1st to 9th Decile with Median Line (PA111)",
    x = "Quarter",
    y = "Gross Monthly Wages (EUR)"
  ) +
  theme_minimal() +
  theme(axis.text.x = element_text(angle = 45, hjust = 1))

Advanced topics

  • rdb: bare-bones interface to DBnomics
  • No metadata information

Manually store metadata alongside the data ::: {.fragment}

# Define query parameters
provider <- "ECB"
dataset <- "MIR"
series <- "M.EE.B.A2C.A.R.A.2250.EUR.N"

# Fetch data
mir <- rdb(provider, dataset, series)

# Manually attach metadata
attr(mir, "provider") <- provider
attr(mir, "dataset") <- dataset
attr(mir, "series")  <- series
attr(mir, "downloaded_at") <- Sys.time()
attr(mir, "source_url") <- paste0("https://api.db.nomics.world/v22/series/", provider, "/", dataset, "/", series)

# Now you can inspect it later
str(attributes(mir))
List of 9
 $ names            : chr [1:31] "@frequency" "Amount category" "AMOUNT_CAT" "Balance sheet item" ...
 $ class            : chr [1:2] "data.table" "data.frame"
 $ row.names        : int [1:299] 1 2 3 4 5 6 7 8 9 10 ...
 $ .internal.selfref:<externalptr> 
 $ provider         : chr "ECB"
 $ dataset          : chr "MIR"
 $ series           : chr "M.EE.B.A2C.A.R.A.2250.EUR.N"
 $ downloaded_at    : POSIXct[1:1], format: "2025-03-28 13:41:05"
 $ source_url       : chr "https://api.db.nomics.world/v22/series/ECB/MIR/M.EE.B.A2C.A.R.A.2250.EUR.N"

:::


meta <- list(
  provider = provider,
  dataset = dataset,
  series  = series,
  downloaded_at = Sys.time()
)
get_rdb_bibentry <- function(provider, dataset, series, format = "biblatex") {
  # Required packages
  if (!requireNamespace("RefManageR", quietly = TRUE)) {
    stop("Package 'RefManageR' is required.")
  }
  if (!requireNamespace("httr", quietly = TRUE) ||
      !requireNamespace("jsonlite", quietly = TRUE)) {
    stop("Packages 'httr' and 'jsonlite' are required.")
  }

  # Build metadata URL
  series_url <- paste0("https://api.db.nomics.world/v22/series/", provider, "/", dataset, "/", series)

  # Fetch metadata
  response <- httr::GET(series_url)
  stopifnot(httr::status_code(response) == 200)
  json_text <- httr::content(response, as = "text", encoding = "UTF-8")
  meta <- jsonlite::fromJSON(json_text)$series$docs

  # Extract fields
  title <- meta$title
  last_update <- as.Date(meta$updated_at)
  year <- format(Sys.Date(), "%Y")
  urldate <- format(Sys.Date(), "%Y-%m-%d")
  citation_key <- gsub("_", "-", paste0(dataset, "-", urldate))

  # Create BibEntry object
  entry <- RefManageR::BibEntry(
    bibtype = "misc",
    key     = citation_key,
    title   = paste0(title, " (", series, ")"),
    url     = series_url,
    language = "english",
    year    = year,
    author  = provider,
    urldate = urldate,
    type    = "Dataset",
    note    = paste0("Accessed ", urldate, ", series last updated ", last_update, ".")
  )

  if (format == "bibtex") {
    return(utils::toBibtex(entry))
  } else if (format == "biblatex") {
    return(RefManageR::toBiblatex(entry))
  } else {
    return(entry)
  }
}
cat(get_rdb_bibentry(
  provider = "ECB",
  dataset = "MIR",
  series   = "M.EE.B.A2C.A.R.A.2250.EUR.N"
))
@Misc{MIR-2025-03-28,   title = { (M.EE.B.A2C.A.R.A.2250.EUR.N)},   url = {https://api.db.nomics.world/v22/series/ECB/MIR/M.EE.B.A2C.A.R.A.2250.EUR.N},   language = {english},   year = {2025},   author = {{ECB}},   urldate = {2025-03-28},   type = {Dataset},   note = {Accessed 2025-03-28, series last updated .}, }